package cn.com.generaldata.os.service.impl;

import java.io.File;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.UnsupportedEncodingException;
import java.net.URLDecoder;
import java.util.List;
import java.util.Properties;
import java.util.Set;

import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.Row;
import org.nutz.dao.Cnd;
import org.nutz.dao.Dao;
import org.nutz.dao.entity.Record;
import org.nutz.dao.impl.sql.SqlTemplate;
import org.nutz.dao.util.cri.SqlExpressionGroup;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import cn.com.generaldata.jsme_indicator_system.entity.GisIndicator;
import cn.com.generaldata.jsme_indicator_system.entity.UnitNz;
import cn.com.generaldata.jsme_indicator_system.entity.UnitPic;
import cn.com.generaldata.os.util.excel.ExportExcel;
import cn.com.generaldata.os.vo.ReportIndicatorQueryVO;

import com.google.common.collect.Lists;
import com.google.common.collect.Sets;

@Service
public class GisShowDataService {

	private static Logger logger = LoggerFactory.getLogger(BIShowDataService.class);
	@Autowired
	private UnitService unitService;
	@Autowired
	private SqlTemplate sqlTemplate;

	@Autowired
	private Dao daoOracle;
	
	@Autowired
	private Dao dao;
	
	/**
	 * 根据条件查询指标数据
	 * */
	public List<GisIndicator> queryGisIndicatorData(GisIndicator gisIndicator){
		List<GisIndicator> list = null;
		
		StringBuffer strSql =  new StringBuffer(255);
		
		strSql.append("select * from t_gis_indicator_info t where 1=1 ");
		
		if (!StringUtils.isEmpty(gisIndicator.getIndicatorArea())){
			strSql.append(" and INDICATOR_AREA='" + gisIndicator.getIndicatorArea() + "'");
		}
		if (!StringUtils.isEmpty(gisIndicator.getIndicatorYear())){
			strSql.append(" and INDICATOR_YEAR='" + gisIndicator.getIndicatorYear() + "'");
		}
		if (!StringUtils.isEmpty(gisIndicator.getIndicatorYype())){
			strSql.append(" and INDICATOR_TYPE='" + gisIndicator.getIndicatorYype() + "'");
		}

		list = sqlTemplate.query(strSql.toString(), null, GisIndicator.class);

		return list;
	}
	
	public String generalTableExcel(ReportIndicatorQueryVO vo) throws FileNotFoundException, IOException {
		//数据表名
		String unitId = vo.getUnitId();
		unitId=unitId.replace(",", "");
		//中文名
		String unitName = vo.getUnitName();
		unitName=unitName.replace(",", "");
		Properties properties = new Properties();
		java.io.InputStream inputstream = getClass().getResourceAsStream("/fileupload.properties");
		try {
			properties.load(inputstream);
		} catch (Exception ex) {
			logger.error("配置文件有误", ex);
		}
		List<Record> records = daoOracle.query(unitId, null);
		List<String> headerList = Lists.newArrayList();
		List<String> headerIdList = Lists.newArrayList();
		Set<String> keySet = Sets.newHashSet();
		if (records != null && records.size() > 0) {
			Record record = records.get(0);
			keySet = record.keySet();
//			for (ReportGroupVO column : columns) {
//				if (keySet.contains(column.getUserIndustrys())) {
			for(String key:keySet){
//					headerList.add(column.getUserIndustrysName());
//					headerIdList.add(column.getUserIndustrys());
					headerList.add(key);
					headerIdList.add(key);
			}
//				}
//			}

			List<List<String>> dataList = Lists.newArrayList();
			for (Record cell : records) {

				List<String> dataRowList = Lists.newArrayList();
				for (String head : headerIdList) {
					dataRowList.add(cell.getString(head));
				}
				dataList.add(dataRowList);
			}

			ExportExcel ee = new ExportExcel(null, headerList);

			for (int i = 0; i < dataList.size(); i++) {
				Row row = ee.addRow();
				for (int j = 0; j < dataList.get(i).size(); j++) {
					ee.addCell(row, j, dataList.get(i).get(j));
				}
			}
			String path = properties.getProperty("fileUploadPath").trim();
			String filePath = path + unitName + ".xlsx";
			ExportExcel writeFile = ee.writeFile(filePath);
			ee.dispose();
			return filePath;
		}
		return null;
	}
	

	public List<String> queryUnitPics(String unitName) {
		List<String> result = Lists.newArrayList();
		Cnd cri=Cnd.where("unitName", "=", unitName);
		List<UnitNz> query = dao.query(UnitNz.class, cri);
		if(query!=null&&query.size()>0){
			UnitNz unitNz = query.get(0);
			SqlExpressionGroup exp1 = Cnd.exps("unitId", "=", unitNz.getId());
			Cnd and = Cnd.where(exp1);
			List<UnitPic> query2 = dao.query(UnitPic.class, and);
			for(UnitPic pic:query2){
				String picId = pic.getId();
				result.add(picId);
			}
		}
		return result;
	}
	
	public String getPic(String id){
		List<UnitPic> list = unitService.showUnitPic(id);

		String path = "";
		if (list != null && list.size() > 0) {
			UnitPic unitPic = list.get(0);
			path = unitPic.getPicPath();
		}
		// 存图片信息
		Properties properties = new Properties();
		java.io.InputStream inputstream = getClass().getResourceAsStream("/fileupload.properties");

		try {
			properties.load(inputstream);
		} catch (Exception ex) {
			logger.error("加载配置文件出错", ex);
		}
		String serverPath = findServerPath();
		String filePath = serverPath + File.separator + path;
		return filePath;
	}
	
	public String findServerPath() {
		String classPath = this.getClass().getClassLoader().getResource("/").getPath();
		try {
			classPath = URLDecoder.decode(classPath, "gb2312");
		} catch (UnsupportedEncodingException e) {
			e.printStackTrace();
		}
		String[] strPath = classPath.split("/");
		String path = "";
		for (int i = 0; i < strPath.length; i++) {
			if (i > 0 && i <= 3) {
				path = path + strPath[i] + "/";
			}
		}
		return path;
	}
}
